package com.bluedot.dao;

import com.bluedot.dao.jdbc.SqlRunner;
import com.bluedot.pojo.pack.PreMappedStatement;
import com.bluedot.pojo.po.*;
import com.bluedot.pojo.vo.*;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * SQL 操作类
 *
 * @author chenzou'quan
 */
public class SQL extends DataQueue {
    private String sql;
    private Connection connection;
    private SqlRunner sqlRunner;

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public SQL() {
        try {
            // 加载hsqldb驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            this.connection = DriverManager
                    .getConnection("jdbc:mysql://rm-bp1mqos7k6lt0a653to.mysql.rds.aliyuncs.com:3306/electrochemicalanalysissystem?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true",
                            "root",
                            "Unlimited2002");
        } catch (Exception e) {
            e.printStackTrace();
        }
        this.sqlRunner = new SqlRunner(connection);
    }



    public SQL(Connection connection) {
        this.connection = connection;
        this.sqlRunner = new SqlRunner(connection);
    }

    private static final HashMap<Class, String> map = new HashMap() {
        {
            put(UserVO.class, "user");
            put(RolePO.class, "role");
            put(RoleDTO.class, "role_user");
            put(RoleVO.class, "roleVO");
            put(RoleAndPermissions.class, "role_permissions");
            put(PermissionsVO.class, "permissionsVO");
            put(MaterialVO.class, "material_vo");
            put(Material.class, "material");
            put(MaterialDetail.class, "material_detail");
            put(Backups.class, "backups");
            put(SysConfig.class, "sys_config");
            put(Algorithm.class, "algorithm");
            put(ReportVO.class, "report_vo");
            put(Report.class, "report");
            put(ReportDetail.class, "report_detail");
            put(RequestVO.class, "request");
            put(MaterialType.class, "material_type");
            put(LogVo.class, "logVo");
            put(Log.class, "log");
            put(AlgorithmVO.class, "algorithm");
            put(AlgorithmHotNumberVO.class, "algorithm");

//            put(AlgorithmHotNumberVO.class, "algorithm order by hot_number desc");
        }
    };

    /**
     * 队列数据库操作实现
     */
    protected void update() {
        PreMappedStatement remove = getDataQueue().remove();
        String sqlString = sql(remove);
        String methodType = remove.getMethodType();

        try {
            boolean add = getReturnQueue().add(doSql(remove.getData().getClass(), methodType, sqlString));
            if (!add) {
                throw new RuntimeException("数据库返回对象添加队列失败");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 普通数据库实现
     */
    protected Object updateData() {
        // 1. 获取缓冲物
        PreMappedStatement remove = this.data;
        // 2. 获取SQL语句
        String sqlString = sql(remove);
        // 3. 获取操作类型
        String methodType = remove.getMethodType();
        // 4. 执行对应SQL
        try {
            Object target = doSql(remove.getData().getClass(), methodType, sqlString);
            if (target != null){
                return this.result = target;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }
    /**
     * 拼接SQL语句
     *
     * @param preMappedStatement 提供包装类
     * @return 返回SQL语句
     */
    private String sql(PreMappedStatement preMappedStatement) {
        com.bluedot.dao.jdbc.SQL tmp = new com.bluedot.dao.jdbc.SQL();
        // 1. 获得SQL语句类型
        String[] methodType = preMappedStatement.getMethodType().split("/");
        // 2. 获得视图
        String table = map.get(preMappedStatement.getData().getClass());
        // 3. 获得筛选字段
        StringBuffer whereString = getWhereString(preMappedStatement);
        switch (methodType[methodType.length - 1]) {
            case "query":
                if (whereString.length() > 0)
                    tmp.SELECT("*").FROM(table).WHERE(whereString.toString());
                else tmp.SELECT("*").FROM(table);
                break;
            case "insert":
                // 4. 拼接insert语句
                String[] ands = whereString.toString().split("AND");
                StringBuffer columns = new StringBuffer();
                StringBuffer values = new StringBuffer();
                for (String and : ands) {
                    String[] split = and.split("=");
                    columns.append(split[0] + ',');
                    values.append(split[1] + ',');
                }
                tmp.INSERT_INTO(table)
                        .VALUES(
                                columns.delete(columns.length() - 1, columns.length()).toString(),
                                values.delete(values.length() - 1, values.length()).toString()
                        );
                break;
            case "update":
                ands = whereString.toString().split("AND");
                String set = whereString.toString().replace("AND", ",");
                tmp.UPDATE(table).SET(set).WHERE(ands[0]);
                break;
            case "delete":
                tmp.DELETE_FROM(table).WHERE(whereString.toString());
                break;
            default:
                throw new IllegalStateException("Unexpected value: " + methodType[methodType.length - 1]);
        }
        return tmp.toString();
    }

    /**
     * 拼接条件语句
     *
     * @param preMappedStatement 提供包装类
     * @return 条件语句
     */
    private StringBuffer getWhereString(PreMappedStatement preMappedStatement) {
        // 1. 创建StringBuffer对象
        StringBuffer whereString = new StringBuffer();
        // 2. 获取传入数据属性
        Class<?> aClass = preMappedStatement.getData().getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        for (Field f : declaredFields) {
            // 3. 获取属性名称
            String fName = f.getName();
            // 4. getter方法获取属性值
            String methodName = "get" + fName.substring(0, 1).toUpperCase() + fName.substring(1);
            Method method;
            Object val;
            try {
                method = aClass.getMethod(methodName);
                val = method.invoke(preMappedStatement.getData());
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
            // 5. 完成数据拼接
            if (val != null) {
                whereString.append(toField(fName)).append("='").append(val).append("' AND ");
            }

        }
        // 6. 删掉最后一个“AND”
        if (whereString.length() > 0)
            whereString.delete(whereString.length() - 5, whereString.length());
        return whereString;
    }

    /**
     * 执行SQL语句
     *
     * @param methodType 方法类型
     * @param sql        SQL语句
     * @return 操作结果
     */
    private Object doSql(Class<?> cla, String methodType, String sql) throws SQLException {
        switch (methodType) {
            case "query":
                List<Map<String, Object>> maps = sqlRunner.selectAll(sql);
                return result(cla, maps);
            case "insert":
                int insert = sqlRunner.insert(sql);
                // 规避自动生成主键的影响
                if (insert == -2147482647) {
                    insert = 1;
                }
                return insert;
            case "update":
                int update = sqlRunner.update(sql);
                return update;
            case "delete":
                int delete = sqlRunner.delete(sql);
                return delete;
            default:
                throw new RuntimeException("执行SQL语句操作");
        }
    }

    /**
     * 返回参数包装类
     *
     * @param cla    目标类
     * @param result 查询map对象
     * @return
     */
    private List<Object> result(Class<?> cla, List<Map<String, Object>> result) {
        List<Object> target = new ArrayList<>();
        for (Map map : result) {
            // 1. 创建的对象
            Object o;
            try {
                // 2. 获取构造方法实例化对象
                o = cla.getConstructor().newInstance();
                // 3. 返回的数组 Field对象反映此表示的属性。
                Field[] declaredFields = cla.getDeclaredFields();
                for (Field field : declaredFields) {
                    String name = field.getName();
                    // 4. 获得属性值
                    Object value = map.get(toField(name).toUpperCase());
                    if (value != null) {
                        // 5. 通过set方法设置
                        String methodName = "set" + name.substring(0, 1).toUpperCase() + name.substring(1);
                        Method method = cla.getMethod(methodName, field.getType());
                        method.invoke(o, value);
                    }
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
            target.add(o);
        }
        return target;
    }

    /**
     * 将属性名转换为表字段名
     *
     * @param field 属性民
     * @return 表字段名
     */
    private String toField(String field) {
        char[] chars = field.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            if (chars[i] <= 'Z' && chars[i] >= 'A') {
                field = field.replace(chars[i] + "", ("_" + chars[i]).toLowerCase());
            }
        }
        return field;
    }

    /**
     * 测试方法
     *
     * @param remove
     * @return
     * @throws SQLException
     */
    protected Object update(PreMappedStatement remove) throws SQLException {
        String sqlString = sql(remove);
        String methodType = remove.getMethodType().split("/")[remove.getMethodType().split("/").length - 1];
        return doSql(remove.getData().getClass(), methodType, sqlString);
    }
}
